package com.worldtvs.online.database;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import com.worldtvs.online.entity.InfoAlbum;
import com.worldtvs.online.entity.InfoVideo;

public class MySQLiteHelper extends SQLiteOpenHelper{
	public static final String DATABASE_NAME = Environment.getExternalStorageDirectory()+"/vdeo.db";
//	public static final String DATABASE_NAME = "vdeo.db";
	public static final int DATABASE_VERSION = 1;
	
	

	
	// tao cac truong trong slide
	public static final String TABLE_SLIDE="table_SLIDE";
	public static final String ID_SLIDE="id_slide";
	public static final String ID_YOUTUBE_SLIDE="id_youtube_slide";
	public static final String IMAGE_SLIDE="image_slide";
	public static final String TITLE_SLIDE="tile_slide";
	public static final String TYPE_SLIDE="type_slide";
	
	
	//table video
	public static final String TABLE_VIDEO = "table_VIDEO";
	public static final String ID_VIDEO="id_video";
	public static final String ID_YOUTUBE_VIDEO="id_youtube_video";
	public static final String IMAGE_VIDEO="image_video";
	public static final String TITLE_VIDEO="title_video";
	public static final String ID_CAT_VIDEO="id_cat_video";
	public static final String VEW_VIDEO="vew_video";
	public static final String DURATION_VIDEO="duration_video";
	
	//table album
	
	public static final String TABLE_ALBUM="table_ALBUM";
	public static final String ID_ALBUM="id_album";
	public static final String TITLE_ALBUM="title_album";
	public static final String IMAGE_ALBUM="image_album";
	public static final String PLAYLISTS_ALBUM="playlists_album";
	public static final String COUNT_ALBUM="cout_album";
	public static final String CAT_ID_ALBUM="cat_id_album";
	//table video
		public static final String TABLE_VIDEO_LIST = "table_VIDEO_LIST";
		public static final String ID_KEY_CHA="id_key_cha";
		public static final String ID_VIDEO_LIST="id_video_list";
		public static final String ID_YOUTUBE_VIDEO_LIST="id_youtube_video_list";
		public static final String IMAGE_VIDEO_LIST="image_video_list";
		public static final String TITLE_VIDEO_LIST="title_video_list";
		public static final String ID_CAT_VIDEO_LIST="id_cat_video_list";
		public static final String VEW_VIDEO_LIST="vew_video_list";
		public static final String DURATION_VIDEO_LIST="duration_video_list";
	
	
	//table total album
		public static final String TABLE_TOTAL_ALBUM="table_TOTAL_ALBUM";
		public static final String ID_TOTAL_ALBUM="id_total_album";
		public static final String TITLE_TOTAL_ALBUM="title_total_album";
		public static final String COUT_TOTAL_ALBUM="count_total_album";
		
	//tao bang video
	String CREATE_TABLE_VDIEO = "CREATE TABLE IF NOT EXISTS " + TABLE_VIDEO + "("+ ID_VIDEO+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+ID_YOUTUBE_VIDEO+ " TEXT, " + IMAGE_VIDEO + " TEXT, "
            + TITLE_VIDEO + " TEXT,  " + VEW_VIDEO + " TEXT,  " + DURATION_VIDEO + " TEXT, "+ ID_CAT_VIDEO + " INTEGER" +")";
	//tao bang album
	String CREATE_TABLE_ALBUM="CREATE TABLE IF NOT EXISTS " + TABLE_ALBUM + "("+ ID_ALBUM+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TITLE_ALBUM + " TEXT," + IMAGE_ALBUM + " TEXT," + PLAYLISTS_ALBUM + " TEXT," + CAT_ID_ALBUM + " TEXT, "+ COUNT_ALBUM + " TEXT" +")";
	
	//tao bang video list
	String CREATE_TABLE_VDIEO_LIST = "CREATE TABLE IF NOT EXISTS " + TABLE_VIDEO_LIST + "("+ ID_VIDEO_LIST+ " INTEGER PRIMARY KEY AUTOINCREMENT,"+ID_KEY_CHA+ " TEXT,  "+ID_YOUTUBE_VIDEO_LIST+ " TEXT, " + IMAGE_VIDEO_LIST + " TEXT, "
            + TITLE_VIDEO_LIST + " TEXT,  " + VEW_VIDEO_LIST + " TEXT,  " + DURATION_VIDEO_LIST + " TEXT, "+ ID_CAT_VIDEO_LIST + " INTEGER" +")";
	
	//tao bang slide
	
	String CREATE_TABLE_SLIDE = "CREATE TABLE IF NOT EXISTS " + TABLE_SLIDE + "("+ ID_SLIDE+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+ID_YOUTUBE_SLIDE+ " TEXT, " + IMAGE_SLIDE + " TEXT, "
            + TITLE_SLIDE + " TEXT, " + TYPE_SLIDE + " INTEGER" +")";
	
	// tao bang total album
	String CREATE_TABLE_TOTAL_ALBUM = "CREATE TABLE IF NOT EXISTS " + TABLE_TOTAL_ALBUM + "("+ ID_TOTAL_ALBUM+ " INTEGER PRIMARY KEY AUTOINCREMENT,"+COUT_TOTAL_ALBUM+ " TEXT,  " + TITLE_TOTAL_ALBUM + " TEXT" +")";
	
	public MySQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_SLIDE);
		db.execSQL(CREATE_TABLE_VDIEO);
		db.execSQL(CREATE_TABLE_ALBUM);
		db.execSQL(CREATE_TABLE_VDIEO_LIST);
		db.execSQL(CREATE_TABLE_TOTAL_ALBUM);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_SLIDE);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_VDIEO);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_ALBUM);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_VDIEO_LIST);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_TOTAL_ALBUM);
		onCreate(db);
	}
//	public ArrayList<InfoAlbum> getIdAlbum(String id){
//		SQLiteDatabase db = this.getWritableDatabase();
//		ArrayList<InfoAlbum> items = new ArrayList<InfoAlbum>();
//		String selectQuery = "SELECT * FROM " + TABLE_ALBUM+ " WHERE "+CAT_ID_ALBUM+ "= "+ id ;
//		Cursor cursor = db.rawQuery(selectQuery, null);
//		if (cursor.moveToFirst()){
//			do {
//				InfoAlbum mAlbum=new InfoAlbum();
//				mAlbum.setAid(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.ID_ALBUM)));
//				mAlbum.setaName(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.TITLE_ALBUM)));
//				mAlbum.setaAvatar(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.IMAGE_ALBUM)));
//				mAlbum.setCat_id(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.CAT_ID_ALBUM)));
//				items.add(mAlbum);
////				
//			} while (cursor.moveToNext());
//			
//		}
//		cursor.close();
//		db.close();
//		return items;
//	}
	public InfoAlbum getAlbumId(String cat_id) {
		
	    SQLiteDatabase db = this.getReadableDatabase();
	    String selectQuery = "SELECT  * FROM " + TABLE_ALBUM + " WHERE "
	            + CAT_ID_ALBUM + " = " + cat_id;
	    Cursor c = db.rawQuery(selectQuery, null);
	 
	    if (c != null)
	        c.moveToFirst();
	 
	    InfoAlbum td = new InfoAlbum();
	    td.setAid(c.getString(c.getColumnIndex(ID_ALBUM)));
	    td.setaName((c.getString(c.getColumnIndex(TITLE_ALBUM))));
	    td.setaAvatar(c.getString(c.getColumnIndex(IMAGE_ALBUM)));
	    td.setCat_id(c.getString(c.getColumnIndex(CAT_ID_ALBUM)));
	    return td;
	}
	public ArrayList<InfoVideo> getAllMsgHis(String relation){
		ArrayList<InfoVideo> arrVideos = new ArrayList<InfoVideo>();
		String selectQuery = "SELECT * FROM " + TABLE_VIDEO_LIST+ " WHERE "+ID_KEY_CHA+ "= "+ relation ;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		// duyet tat ca ca hang de add vao list
				if (c.moveToFirst()) {
					do {
						InfoVideo objVideo = new InfoVideo();
						objVideo.setVid(c.getString(c.getColumnIndex(ID_VIDEO_LIST)));
						objVideo.setvCatId(String.valueOf(c.getString(c.getColumnIndex(ID_CAT_VIDEO_LIST))));
						objVideo.setvDuration(String.valueOf(c.getString(c.getColumnIndex(DURATION_VIDEO_LIST))));
						objVideo.setvName(String.valueOf(c.getString(c.getColumnIndex(TITLE_VIDEO_LIST))));
						objVideo.setvView(String.valueOf(c.getString(c.getColumnIndex(VEW_VIDEO_LIST))));
						objVideo.setvYoutube(String.valueOf(c.getString(c.getColumnIndex(ID_YOUTUBE_VIDEO_LIST))));
						objVideo.setvAvatar(String.valueOf(c.getString(c.getColumnIndex(IMAGE_VIDEO_LIST))));
						// add vao list
						arrVideos.add(objVideo);
					} while (c.moveToNext());
				}
				c.close();
				if (db != null){
					db.close();	
				}
		return arrVideos;
	}
	public ArrayList<InfoAlbum> getAllAlbum(String relation){
		ArrayList<InfoAlbum> arrVideos = new ArrayList<InfoAlbum>();
		String selectQuery = "SELECT * FROM " + TABLE_ALBUM+ " WHERE "+CAT_ID_ALBUM+ "= "+ relation ;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		// duyet tat ca ca hang de add vao list
				if (c.moveToFirst()) {
					do {
						InfoAlbum objVideo = new InfoAlbum();
						objVideo.setAid(c.getString(c.getColumnIndex(ID_ALBUM)));
						objVideo.setaAvatar(c.getString(c.getColumnIndex(IMAGE_ALBUM)));
						objVideo.setCat_id(c.getString(c.getColumnIndex(CAT_ID_ALBUM)));
						objVideo.setaName(c.getString(c.getColumnIndex(TITLE_ALBUM)));
						// add vao list
						arrVideos.add(objVideo);
					} while (c.moveToNext());
				}
				c.close();
				if (db != null){
					db.close();	
				}
		return arrVideos;
	}
}
